#! /bin/bash

/usr/bin/hive -S -e "
insert overwrite table dwd.dim_clazz
select id,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       dt
from ods.t_itcast_clazz;

insert overwrite table dwd.dim_department
select e.id,
       sd.name,
       sd.dt
from ods.t_employee e
         left join ods.t_scrm_department sd
                   on e.tdepart_id = sd.id;

insert overwrite table dwd.fact_customer_relationship
select tcr.id,
       substr(tcr.create_date_time, 1, 10),
       if(ca.appeal_status is null, 2, ca.appeal_status),
       tcr.origin_type,
       tcr.origin_channel,
       if(tcr.payment_state = 'PAID', 1, 0),
       substr(tcr.payment_time, 1, 10),
       tcr.itcast_clazz_id,
       tcr.itcast_school_id,
       tcr.itcast_subject_id,
       tcr.creator,
       tcr.dt
from ods.t_customer_relationship as tcr
         left join ods.t_customer_appeal as ca
                   on tcr.id = ca.customer_relationship_first_id;

"